# This is a sample Python script.

# Press Shift+F10 to execute it or replace it with your code.
# Press Double Shift to search everywhere for classes, files, tool windows, actions, and settings.


def print_hi(name):
    # Use a breakpoint in the code line below to debug your script.
    print(f'Hi, {name}')  # Press Ctrl+F8 to toggle the breakpoint.


# Press the green button in the gutter to run the script.
if __name__ == '__main__':
    print_hi('PyCharm')

# See PyCharm help at https://www.jetbrains.com/help/pycharm/
import numpy as np
import pandas as pd


def convert(x):
    t = str(x)
    field = t.split("'")

    if len(field) == 1:
        return float(field[0])
    else:
        return float(field[0]) * 60 + float(field[1])


def convert3(x):
    t = str(x)
    field = t.split(".")

    if len(field) == 1:
        return float(field[0])
    else:
        return float(field[0]) * 60 + float(field[1])


def convert2(x):
    t = str(x)
    field = t.split("'")

    if len(field) == 1:
        return float(field[0])
    elif field[1].__contains__('"'):
        f = field[1].split('"')
        return float(field[0]) * 60 + float(f[0])
    else:

        return float(field[0]) * 60 + float(field[1])


def run(x):
    x.replace(x.min(), 0, inplace=True)
    x.replace(x.max(), 1000, inplace=True)

    return x
def paobu(x,y,z):
    s1 = pd.Series([1])

    s1 = s1.append(x)

    a = pd.cut(z.apply(lambda x: 1500 if x == 0 else x),
               bins=run(s1),

               labels=y)
    return a
def ticao(x,y,z):

    s1 = pd.Series([10000])
    x = x.append(s1)
    a = pd.cut(z,
               bins=x,

               labels=y)
    return a

test1_boy = pd.read_excel(r'C:\Users\Jerry Peng\Desktop\培训学习\第五章\python 自动化办公\Pandas数据分析库\18级高一体测成绩汇总.xls',
                          header=0)
test1_girl = pd.read_excel(r'C:\Users\Jerry Peng\Desktop\培训学习\第五章\python 自动化办公\Pandas数据分析库\18级高一体测成绩汇总.xls',
                           sheet_name=1, header=0)
score_standard = pd.read_excel(r'C:\Users\Jerry Peng\Desktop\培训学习\第五章\python 自动化办公\Pandas数据分析库\体侧成绩评分表.xls',
                               header=[0, 1])
pd.set_option('display.max_columns', 1000)
pd.set_option('display.width', 1000)
pd.set_option('display.max_colwidth', 1000)
pd.set_option('display.unicode.ambiguous_as_wide', True)
pd.set_option('display.unicode.east_asian_width', True)


print()
# 男1000米跑变成float类型的值
test1_boy.loc[:, '男1000米跑'] = test1_boy.loc[:, '男1000米跑'].apply(convert)
# 女800米跑变成float类型的值
test1_girl.loc[:, '女800米跑']=test1_girl.loc[:, '女800米跑'].apply(convert3)

# 评分标准中男1000米跑和女800米跑的成绩变成float类型的值
score_standard.loc[:, ('男1000米跑', '成绩')] = score_standard.loc[:, '男1000米跑'].loc[:, '成绩'].apply(convert2)
score_standard.loc[:, ('女800米跑', '成绩')] = score_standard.loc[:, '女800米跑'].loc[:, '成绩'].apply(convert2)

# 其他所有数值类型的值，都要转换为float类型的值
test1_boy.iloc[:, 2:-1] = test1_boy.iloc[:, 2:-1].applymap(lambda x: float(x))
test1_girl.iloc[:, 2:-1] = test1_girl.iloc[:, 2:-1].applymap(lambda x: float(x))

# score_standard['男1000米跑','成绩']=score_standard.loc[:,('男1000米跑','成绩')]=score_standard[][]
# 将数据转化为成绩

# print(a)
boy_1000=paobu(score_standard.loc[:, ('男1000米跑', '成绩')],score_standard.loc[0:19, ('男1000米跑', '分数')],test1_boy.iloc[:, 2])
# print(boy_1000)
boy_50=paobu(score_standard.loc[:, ('男50米跑', '成绩')],score_standard.loc[0:19, ('男50米跑', '分数')],test1_boy.iloc[:, 3])
# print(boy_50)
boy_jump=ticao(score_standard.loc[::-1, ('男跳远', '成绩')],score_standard.loc[::-1, ('男跳远', '分数')],test1_boy.iloc[:, 4])
cond=score_standard.loc[:,('男引体', '成绩')] <800
boy_tqq=ticao(score_standard.loc[::-1, ('男体前屈', '成绩')],score_standard.loc[::-1, ('男体前屈', '分数')],test1_boy.iloc[:, 5])
x=score_standard.loc[::-1,('男引体', '成绩')].copy()
boy_yt=ticao(x[cond],
             score_standard.loc[::-1, ('男引体', '分数')][cond],test1_boy.iloc[:, 6])
boy_fhl=ticao(score_standard.loc[::-1, ('男肺活量', '成绩')],score_standard.loc[::-1, ('男肺活量', '分数')],test1_boy.iloc[:, 7])

girl_800=paobu(score_standard.loc[:, ('女800米跑', '成绩')],score_standard.loc[0:19, ('女800米跑', '分数')],test1_girl.iloc[:,2])
# print(girl_800)
girl_50=paobu(score_standard.loc[:, ('女50米跑', '成绩')],score_standard.loc[0:19, ('女50米跑', '分数')],test1_girl.iloc[:, 3])
# print(girl_50)
girl_jump=ticao(score_standard.loc[::-1, ('女跳远', '成绩')],score_standard.loc[::-1, ('女跳远', '分数')],test1_girl.iloc[:, 4])
cond=score_standard.loc[:,('女仰卧', '成绩')] <800
girl_tqq=ticao(score_standard.loc[::-1, ('女体前屈', '成绩')],score_standard.loc[::-1, ('女体前屈', '分数')],test1_girl.iloc[:, 5])
x=score_standard.loc[::-1,('女仰卧', '成绩')].copy()
girl_yt=ticao(x[cond],
             score_standard.loc[::-1, ('女仰卧', '分数')][cond],test1_girl.iloc[:, 6])
girl_fhl=ticao(score_standard.loc[::-1, ('女肺活量', '成绩')],score_standard.loc[::-1, ('女肺活量', '分数')],test1_girl.iloc[:, 7])

boy_info=pd.concat([test1_boy.iloc[:, 0:1],test1_boy.iloc[:, 2],boy_1000,test1_boy.iloc[:, 3],boy_50,
                    test1_boy.iloc[:, 4],boy_jump,test1_boy.iloc[:, 5],boy_tqq,
                    test1_boy.iloc[:, 6],boy_yt,test1_boy.iloc[:, 7],boy_fhl],axis=1)
print(boy_info)
girl_info=pd.concat([test1_girl.iloc[:, 0:1],test1_girl.iloc[:, 2],girl_800,test1_girl.iloc[:, 3],girl_50,
                    test1_girl.iloc[:, 4],girl_jump,test1_girl.iloc[:, 5],girl_tqq,
                    test1_girl.iloc[:, 6],girl_yt,test1_girl.iloc[:, 7],girl_fhl],axis=1)
print(girl_info)
